package ext.drawing;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import ext.drawing.bean.DrawingsBorrowBean;
import wt.method.MethodContext;
import wt.pom.Transaction;
import wt.pom.WTConnection;
import wt.util.WTException;

/**
 * 中间表db业务类
 * 
 * @author lilin
 * 
 */
public class BorrowUtil {

	public static String REVIEW = "REVIEW";// 审批中
	public static String BORROW = "BORROW";// 借阅中（权限生效中）
	public static String EXPIRE = "EXPIRE";// 失效（已回收权限）

	/**
	 * 根据图纸编号、申请人姓名 查找申请表单
	 * 
	 * @param docName
	 * @param number
	 * @return
	 * @throws WTException
	 */
	public static List<DrawingsBorrowBean> getDrawingBorrowByNumberAndName(String number, String userName,
			String version) throws WTException {
		WTConnection wtconnection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<DrawingsBorrowBean> list = new ArrayList<DrawingsBorrowBean>();
		try {
			MethodContext methodcontext = MethodContext.getContext();
			wtconnection = (WTConnection) methodcontext.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("select *");
			sql.append(" from TM_DRAWINGSBORROW");
			sql.append(" where DRAWINGSBORROWNUMBER ='" + number + "' ");
			sql.append(" and BORROWUSER ='" + userName + "' ");
			sql.append(" and DRAWINGSBORROWVERSION ='" + version + "' ");
			ps = wtconnection.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				DrawingsBorrowBean db = new DrawingsBorrowBean();
				db.setId(rs.getString("ID"));
				db.setDrawingsBorrowName(rs.getString("DRAWINGSBORROWNAME"));
				db.setDrawingsBorrowNumber(rs.getString("DRAWINGSBORROWNUMBER"));
				db.setDrawingsBorrowVersion(rs.getString("DRAWINGSBORROWVERSION"));
				db.setBorrowDate(rs.getString("BORROWDATE"));
				db.setBorrowUser(rs.getString("BORROWUSER"));
				db.setBorrowState(rs.getString("BORROWSTATE"));
				db.setBorrowRejectOpinion(rs.getString("BORROWREJECTOPINION"));
				db.setBorrowBeginDate(rs.getString("BORROWBEGINDATE"));
				db.setBorrowEndDate(rs.getString("BORROWENDDATE"));
				db.setCreateUser(rs.getString("CREATEUSER"));
				db.setCreateDate(rs.getString("CREATEDATE"));
				db.setAttr1(rs.getString("ATTR1"));
				db.setAttr2(rs.getString("ATTR2"));
				list.add(db);
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 根据图纸编号、申请人姓名以及版本 查找申请表单
	 * 
	 * @param number
	 * @param userName
	 * @param version
	 * @return
	 * @throws WTException
	 */
	public static List<DrawingsBorrowBean> getDrawingBorrowByNumberAndNameAndVersion(String number, String userName,
			String version) throws WTException {
		WTConnection wtconnection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<DrawingsBorrowBean> list = new ArrayList<DrawingsBorrowBean>();
		try {
			MethodContext methodcontext = MethodContext.getContext();
			wtconnection = (WTConnection) methodcontext.getConnection();
			StringBuffer sql = new StringBuffer();

			sql.append("select *");
			sql.append(" from TM_DRAWINGSBORROW");
			sql.append(" where DRAWINGSBORROWNUMBER ='" + number + "' ");
			sql.append(" and DRAWINGSBORROWVERSION ='" + version + "' ");
			sql.append(" and BORROWUSER ='" + userName + "' ");

			ps = wtconnection.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				DrawingsBorrowBean db = new DrawingsBorrowBean();
				db.setId(rs.getString("ID"));
				db.setDrawingsBorrowName(rs.getString("DRAWINGSBORROWNAME"));
				db.setDrawingsBorrowNumber(rs.getString("DRAWINGSBORROWNUMBER"));
				db.setDrawingsBorrowVersion(rs.getString("DRAWINGSBORROWVERSION"));
				db.setBorrowDate(rs.getString("BORROWDATE"));
				db.setBorrowUser(rs.getString("BORROWUSER"));
				db.setBorrowState(rs.getString("BORROWSTATE"));
				db.setBorrowRejectOpinion(rs.getString("BORROWREJECTOPINION"));
				db.setBorrowBeginDate(rs.getString("BORROWBEGINDATE"));
				db.setBorrowEndDate(rs.getString("BORROWENDDATE"));
				db.setCreateUser(rs.getString("CREATEUSER"));
				db.setCreateDate(rs.getString("CREATEDATE"));
				db.setAttr1(rs.getString("ATTR1"));
				db.setAttr2(rs.getString("ATTR2"));
				list.add(db);
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 根据id查找申请表单
	 * 
	 * @param docName
	 * @param number
	 * @return
	 * @throws WTException
	 */
	public static List<DrawingsBorrowBean> getDrawingBorrowById(String id) throws WTException {
		WTConnection wtconnection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<DrawingsBorrowBean> list = new ArrayList<DrawingsBorrowBean>();
		try {
			MethodContext methodcontext = MethodContext.getContext();
			wtconnection = (WTConnection) methodcontext.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("select *");
			sql.append(" from TM_DRAWINGSBORROW");
			sql.append(" where ID ='" + id + "' ");
			// sql.append(" and BORROWUSER ='" + userName + "' ");
			ps = wtconnection.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				DrawingsBorrowBean db = new DrawingsBorrowBean();
				db.setId(rs.getString("ID"));
				db.setDrawingsBorrowName(rs.getString("DRAWINGSBORROWNAME"));
				db.setDrawingsBorrowNumber(rs.getString("DRAWINGSBORROWNUMBER"));
				db.setDrawingsBorrowVersion(rs.getString("DRAWINGSBORROWVERSION"));
				db.setBorrowDate(rs.getString("BORROWDATE"));
				db.setBorrowUser(rs.getString("BORROWUSER"));
				db.setBorrowState(rs.getString("BORROWSTATE"));
				db.setBorrowRejectOpinion(rs.getString("BORROWREJECTOPINION"));
				db.setBorrowBeginDate(rs.getString("BORROWBEGINDATE"));
				db.setBorrowEndDate(rs.getString("BORROWENDDATE"));
				db.setCreateUser(rs.getString("CREATEUSER"));
				db.setCreateDate(rs.getString("CREATEDATE"));
				db.setAttr1(rs.getString("ATTR1"));
				db.setAttr2(rs.getString("ATTR2"));
				list.add(db);
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 根据id查找申请表单
	 * 
	 * @param docName
	 * @param number
	 * @return
	 * @throws WTException
	 */
	public static List<DrawingsBorrowBean> getDrawingBorrowByNumber(String number) throws WTException {
		WTConnection wtconnection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<DrawingsBorrowBean> list = new ArrayList<DrawingsBorrowBean>();
		try {
			MethodContext methodcontext = MethodContext.getContext();
			wtconnection = (WTConnection) methodcontext.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("select *");
			sql.append(" from TM_DRAWINGSBORROW");
			sql.append(" where DRAWINGSBORROWNUMBER ='" + number + "' ");
			sql.append(" and BORROWSTATE ='" + BorrowUtil.BORROW + "' ");
			// sql.append(" and BORROWUSER ='" + userName + "' ");
			ps = wtconnection.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				DrawingsBorrowBean db = new DrawingsBorrowBean();
				db.setId(rs.getString("ID"));
				db.setDrawingsBorrowName(rs.getString("DRAWINGSBORROWNAME"));
				db.setDrawingsBorrowNumber(rs.getString("DRAWINGSBORROWNUMBER"));
				db.setDrawingsBorrowVersion(rs.getString("DRAWINGSBORROWVERSION"));
				db.setBorrowDate(rs.getString("BORROWDATE"));
				db.setBorrowUser(rs.getString("BORROWUSER"));
				db.setBorrowState(rs.getString("BORROWSTATE"));
				db.setBorrowRejectOpinion(rs.getString("BORROWREJECTOPINION"));
				db.setBorrowBeginDate(rs.getString("BORROWBEGINDATE"));
				db.setBorrowEndDate(rs.getString("BORROWENDDATE"));
				db.setCreateUser(rs.getString("CREATEUSER"));
				db.setCreateDate(rs.getString("CREATEDATE"));
				db.setAttr1(rs.getString("ATTR1"));
				db.setAttr2(rs.getString("ATTR2"));
				list.add(db);
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 将图文档申请数据存入数据库
	 * 
	 * @param darwingName
	 * @param drawingNumber
	 * @param userName
	 * @param time
	 * @param version
	 * @return
	 */
	public static Boolean createDrawingBorrow(String darwingName, String drawingNumber, String userName, String time,
			String version, String cause, String reason) {
		WTConnection wtconnection = null;
		PreparedStatement ps = null;
		int rs = 0;
		Transaction trans = new Transaction();
		try {

			trans.start();
			MethodContext methodcontext = MethodContext.getContext();
			wtconnection = (WTConnection) methodcontext.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append(
					"insert into TM_DRAWINGSBORROW (DRAWINGSBORROWNAME,DRAWINGSBORROWNUMBER,BORROWUSER,BORROWDATE,DRAWINGSBORROWVERSION,BORROWSTATE,ATTR1,ATTR2)");
			sql.append(" values ('" + darwingName + "','" + drawingNumber + "','" + userName + "','" + time + "','"
					+ version + "','" + BorrowUtil.REVIEW + "','" + cause + "','" + reason + "') ");
			ps = wtconnection.prepareStatement(sql.toString());
			rs = ps.executeUpdate();
			if (rs != 0) {

			}

			ps.close();
			trans.commit();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			trans.rollback();
			return false;
		}
	}

	/**
	 * 根据文档编号和用户名 修改 借阅表的数据状态
	 * 
	 * @param state
	 *            状态 ：REVIEW、BORROW、EXPIRE
	 * @param number
	 *            文档编号
	 * @param user
	 *            申请人用户名
	 */
	public static void updateDrawingBorrowState(String state, String number, String user, String version) {
		WTConnection wtconnection = null;
		PreparedStatement ps = null;
		int rs = 0;
		Transaction trans = new Transaction();
		try {

			trans.start();
			MethodContext methodcontext = MethodContext.getContext();
			wtconnection = (WTConnection) methodcontext.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("update TM_DRAWINGSBORROW set Borrowstate = '" + state + "' where drawingsborrownumber='"
					+ number + "' and DRAWINGSBORROWVERSION='" + version + "' and BORROWSTATE='" + BorrowUtil.BORROW
					+ "' and  borrowuser='" + user + "'");
			ps = wtconnection.prepareStatement(sql.toString());
			rs = ps.executeUpdate();
			if (rs != 0) {
			}

			ps.close();
			trans.commit();
		} catch (Exception e) {
			e.printStackTrace();
			trans.rollback();
		}
	}

	/**
	 * 根据文档编号和用户名 和就版本 修改 借阅表的数据状态和时间以及新版本
	 * 
	 * @param state
	 *            状态 ：REVIEW、BORROW、EXPIRE
	 * @param beginTimeStr
	 *            借阅开始时间 yyyy-MM-dd HH:mm:ss
	 * @param endTimeStr
	 *            借阅结束时间 yyyy-MM-dd HH:mm:ss
	 * @param number
	 *            文档编号
	 * @param user
	 *            申请人用户名
	 * @param oldVersion
	 *            旧版本
	 * @param newVersion
	 *            新版本
	 */
	public static void updateDrawingBorrowVerionAndStateAndDate(String state, String beginTimeStr, String endTimeStr,
			String number, String user, String oldVersion, String newVersion) {
		WTConnection wtconnection = null;
		PreparedStatement ps = null;
		int rs = 0;
		Transaction trans = new Transaction();
		try {

			trans.start();
			MethodContext methodcontext = MethodContext.getContext();
			wtconnection = (WTConnection) methodcontext.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("update TM_DRAWINGSBORROW set Borrowstate = '" + state + "',Borrowbegindate='" + beginTimeStr
					+ "',borrowenddate='" + endTimeStr + "',DRAWINGSBORROWVERSION='" + newVersion
					+ "' where drawingsborrownumber='" + number + "' and DRAWINGSBORROWVERSION='" + oldVersion
					+ "' and BORROWSTATE='" + BorrowUtil.REVIEW + "' and  borrowuser='" + user + "'");
			ps = wtconnection.prepareStatement(sql.toString());
			rs = ps.executeUpdate();
			if (rs != 0) {
			}
			ps.close();
			trans.commit();
		} catch (Exception e) {
			e.printStackTrace();
			trans.rollback();
		}
	}

	/**
	 * 根据版本、编号、用户更新借阅天数
	 * 
	 * @param day
	 * @param number
	 * @param user
	 * @param ver
	 */
	public static void updateDrawingBorrowDay(String day, String number, String user, String ver) {
		WTConnection wtconnection = null;
		PreparedStatement ps = null;
		int rs = 0;
		Transaction trans = new Transaction();
		try {

			trans.start();
			MethodContext methodcontext = MethodContext.getContext();
			wtconnection = (WTConnection) methodcontext.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("update TM_DRAWINGSBORROW set borrowdate = '" + day + "' where drawingsborrownumber='" + number
					+ "' and drawingsborrowversion='" + ver + "' and  borrowuser='" + user + "'");
			ps = wtconnection.prepareStatement(sql.toString());
			rs = ps.executeUpdate();
			if (rs != 0) {
			}
			ps.close();
			trans.commit();
		} catch (Exception e) {
			e.printStackTrace();
			trans.rollback();
		}
	}

	/**
	 * 根据文档编号和用户名 修改 借阅表的借阅天数
	 * 
	 * @param state
	 *            状态 ：REVIEW、BORROW、EXPIRE
	 * @param beginTimeStr
	 *            借阅开始时间 yyyy-MM-dd HH:mm:ss
	 * @param endTimeStr
	 *            借阅结束时间 yyyy-MM-dd HH:mm:ss
	 * @param number
	 *            文档编号
	 * @param user
	 *            申请人用户名
	 */
	public static void updateDrawingBorrowVersion(String number, String user, String oldVer, String newVer) {
		WTConnection wtconnection = null;
		PreparedStatement ps = null;
		int rs = 0;
		Transaction trans = new Transaction();
		try {

			trans.start();
			MethodContext methodcontext = MethodContext.getContext();
			wtconnection = (WTConnection) methodcontext.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append(
					"update TM_DRAWINGSBORROW set drawingsborrowversion = '" + newVer + "' where drawingsborrownumber='"
							+ number + "' and drawingsborrowversion='" + oldVer + "' and  borrowuser='" + user + "'");
			ps = wtconnection.prepareStatement(sql.toString());
			rs = ps.executeUpdate();
			if (rs != 0) {
			}
			ps.close();
			trans.commit();
		} catch (Exception e) {
			e.printStackTrace();
			trans.rollback();
		}
	}

}
